using System;
using System.Collections;
using System.Collections.Generic;
using Portal.Data.Entity;
using Portal.Core.Helper;
using System.Linq;
namespace Portal.Data.Helper
{
    public class LINQHelper
    {
        public static List<T> SlicedFindAll<T>(string sql, string orderByClause, int firstResult, int maxResults, out int total)
        {
            var newsql = MakePagingSql(ref sql, ref orderByClause, firstResult, maxResults);

            //get cache count---------
            var prs = new object[] { typeof(T).Name, "SlicedFindAll", sql };
            var obj = CacheHelper.Instance.Get(prs);
            if (obj == null)
            {
                using (var context = new CmsDataContext())
                {
                    total = context.ExecuteQuery<int>(sql).FirstOrDefault();
                    CacheHelper.Instance.Set(prs, total);
                }
            }
            else total = (int)obj;
            using (var context = new CmsDataContext())
            {
                return context.ExecuteQuery<T>(newsql).ToList();
            }
        }
        /// <summary>
        /// Lay sql paging and Convert sql input into sql count total
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="orderByClause"></param>
        /// <param name="start"></param>
        /// <param name="max"></param>
        /// <returns></returns>
        private static string MakePagingSql(ref string sql, ref string orderByClause, int start, int max)
        {
            //remove orderby, groupby
            int iOrder = sql.IndexOf("order by", StringComparison.OrdinalIgnoreCase);
            if (iOrder > 0) sql = sql.Substring(0, iOrder);
            int iGroup = sql.IndexOf("group by", StringComparison.OrdinalIgnoreCase);
            if (iGroup > 0) sql = sql.Substring(0, iGroup);

            if (orderByClause.IndexOf("order by", StringComparison.OrdinalIgnoreCase) < 0)
                orderByClause = "order by" + orderByClause;

            string sSelect = "", sFrom = "";
            if (sql.IndexOf("ROW_NUMBER", StringComparison.OrdinalIgnoreCase) < 0)
            {
                int iFrom = sql.IndexOf("from", StringComparison.OrdinalIgnoreCase);

                sSelect = sql.Substring(0, iFrom);
                sFrom = sql.Substring(iFrom);
                //convert sql input into counter
                sql = "select count(*) " + sFrom;

                var wherePaging = string.Format(" WHERE RowNumber BETWEEN {0} AND {1}", start + 1, start + max);

                var newSql = @"WITH tab AS(";
                newSql += string.Format("{0}, ROW_NUMBER() OVER ({1}) AS RowNumber {2}", sSelect, orderByClause, sFrom);
                newSql += @") ";
                newSql += string.Format("select tab.* FROM tab {0} ", wherePaging);
                //                newSql += string.Format("select tab.* FROM tab {0} {1}", sWhere, orderByClause);
                return newSql;
            }
            return sSelect + " " + sFrom + " " + orderByClause;
        }


        public static List<T> GetList<T>(string sql, params object[] parameters)
        {
            using (var context = new CmsDataContext())
            {
                return context.ExecuteQuery<T>(sql, parameters).ToList();
            }
        }

        public static T GetFirst<T>(string sql, params object[] parameters)
        {
            using (var context = new CmsDataContext())
            {
                return context.ExecuteQuery<T>(sql, parameters).FirstOrDefault();
            }
        }

        public static int ExecuteNonQuery(string sql, params object[] parameters)
        {
            using (var context = new CmsDataContext())
            {
                return context.ExecuteCommand(sql, parameters);
            }
        }
    }

}
